#!/usr/bin/env node

/**
 * 数据库初始化脚本
 * 用于手动初始化 SQLite3 数据库
 */

import sqlite3 from 'sqlite3';
import { promisify } from 'util';
import path from 'path';
import fs from 'fs';

// 数据库配置
const DB_PATH = process.env.DATABASE_PATH || path.join(process.cwd(), 'data', 'flowise.db');

// 确保数据目录存在
const dataDir = path.dirname(DB_PATH);
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true });
}

// 创建数据库连接
const db = new sqlite3.Database(DB_PATH);

// 将回调风格的 API 转换为 Promise
const dbRun = promisify(db.run.bind(db));

// 数据库初始化
async function initializeDatabase() {
  try {
    // 创建会话表
    await dbRun(`
      CREATE TABLE IF NOT EXISTS sessions (
        id TEXT PRIMARY KEY,
        chatflow_id TEXT NOT NULL,
        user_id TEXT,
        start_time TEXT NOT NULL,
        end_time TEXT,
        message_count INTEGER DEFAULT 0,
        status TEXT DEFAULT 'active',
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 创建消息表
    await dbRun(`
      CREATE TABLE IF NOT EXISTS messages (
        id TEXT PRIMARY KEY,
        session_id TEXT NOT NULL,
        role TEXT NOT NULL,
        content TEXT NOT NULL,
        timestamp TEXT NOT NULL,
        metadata TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (session_id) REFERENCES sessions (id)
      )
    `);

    // 创建执行追踪表
    await dbRun(`
      CREATE TABLE IF NOT EXISTS execution_traces (
        id TEXT PRIMARY KEY,
        session_id TEXT NOT NULL,
        step_name TEXT NOT NULL,
        step_type TEXT NOT NULL,
        input_data TEXT,
        output_data TEXT,
        status TEXT DEFAULT 'pending',
        error_message TEXT,
        duration INTEGER,
        timestamp TEXT NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (session_id) REFERENCES sessions (id)
      )
    `);

    // 创建索引
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_sessions_chatflow_id ON sessions (chatflow_id)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_sessions_status ON sessions (status)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages (session_id)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages (timestamp)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_traces_session_id ON execution_traces (session_id)`);
    await dbRun(`CREATE INDEX IF NOT EXISTS idx_traces_timestamp ON execution_traces (timestamp)`);

    console.log('数据库初始化完成');
  } catch (error) {
    console.error('数据库初始化失败:', error);
    throw error;
  }
}

// 关闭数据库连接
function closeDatabase() {
  return new Promise((resolve, reject) => {
    db.close((err) => {
      if (err) {
        reject(err);
      } else {
        resolve();
      }
    });
  });
}

async function main() {
  try {
    console.log('正在初始化数据库...');
    await initializeDatabase();
    console.log('数据库初始化完成！');
    console.log('数据库文件位置:', DB_PATH);
  } catch (error) {
    console.error('数据库初始化失败:', error);
    process.exit(1);
  } finally {
    await closeDatabase();
  }
}

main();